/**
 * Description:
 * Class Name:ExecuteSQL
 * Date:2022/4/4 18:36
 *
 * @author LTisme
 * @version JAVA17
 */
package 疯狂Java讲义.第13章_MySQL数据库和JDBC编程._13_4_执行SQL语句的方式._13_4_2_使用execute方法执行SQL语句;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class ExecuteSQL {
    private String driver;
    private String url;
    private String user;
    private String pwd;

    public void initParam(String paramFile) throws IOException{
        var props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pwd = props.getProperty("pwd");
    }

    public void executeSql(String sql) throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        try(
                Connection connection = DriverManager.getConnection(url, user, pwd);
                Statement statement = connection.createStatement()
                ){
            // 开始执行SQL语句
            boolean hasResultSet = statement.execute(sql);
            if (hasResultSet) {
                try(
                        // 获取结果
                        ResultSet resultSet = statement.getResultSet()
                ){
                    // ResultSetMetaData是用于分析结果集的元数据接口
                    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                    int columnCount = resultSetMetaData.getColumnCount();
                    // 迭代输出ResultSet对象
                    while (resultSet.next()){
                        // 依次输出每列的值
                        for (int i = 0; i < columnCount; i++) {
                            System.out.println(resultSet.getString(i + 1) + "\t");      // ①
                        }
                        System.out.println("\n");
                    }
                }
            }
            else {
                System.out.println("该SQL语句影响的记录有 " + statement.getUpdateCount() + " 条");
            }
        }
    }

    public static void main(String[] args) throws IOException, SQLException, ClassNotFoundException {
        var es = new ExecuteSQL();
        es.initParam("./Chapter_13_mysql.ini");
        System.out.println("-----执行删除表的DDL语句-----");
        es.executeSql("drop table if exists my_test;");
        System.out.println("-----执行建表的DML语句-----");
        es.executeSql("create table my_test(" +
                "test_id int auto_increment primary key," +
                "test_name varchar(255));");
        System.out.println("-----执行插入数据的DML语句-----");
        es.executeSql("insert into my_test (test_name)" +
                " select student_name from student_table;");
        System.out.println("-----执行查询数据的查询语句------");
        es.executeSql("select * from my_test;");
    }
}
